
/*
*Pool Public Licence version 0.1 or later
*Released on 20th April 2008.
*Any voilations can be repoted at paritosh@wikiocean.net
*What is treated as voilations can be found at www.wikiocean.net/ppl1-voilations* 
******************************************************************************
* The contents of this file are subject Affero GPL v3 and later with additional conditions.
* The additional conditions are to be considered as part of the Affero GPL v3 and later.
* The additional conditions will supersede the Affero GPLv3 and later in case of conflict.
* ("License"); You may not use this file except in compliance with the License
* You may obtain a copy of the License at http://www.gnu.org/licenses/agpl.html
* Software distributed under the License is distributed on an "AS IS" basis,
* WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
* the specific language governing rights and limitations under the License.
* The Initial Developer of the Original Code is Paritosh Pungaliya.
* created by Paritosh Pungaliya are Copyright (C) Paritosh Pungaliya; All Rights Reserved.
* Contributions are Copyright (C) 2001-2008 Paritosh Pungaliya.

* Objective of the additional terms (license) 
* 1) Is to extend the software freedom to freedom to work.
* 2) To ensure that knowledge is free from monopoly of agencie(s).
* 3) To avoid a situation where big corporates or investors can buy out free software groups and companies and then start milking the communities built around it.
* (this trend can be seen how the openness changes in companies that get Venture Capital funding.)
* (if you agree to this logic I invite you to our group to work together)

* Additional conditions
*  0) Source code will also include
* 	a) data dictionaries, data ontologies which are used to alter the behavior of code or to control           the features of application.
* 	b) Any code generated or derived automatically or manually from this code or logic
*  1) The revenue generated by deploying, implementing , selling services and other activities
*  	 based on this source code should be shared 100%, between the people working on it.
*  2) Capital invested should be given only fixed rate of return or linked to revenue/surplus growth for limited time.
*  3) People working on the project should always have an option to replace the capital provider in full or part with lower cost capital.
*  4) Their should not be any ownership which can be sold as a claim on future profit to be generated from the work of people working on the code.
*  5) Brand thus generated should belong to the group.
*  6) For old and new members revenue sharing should be equal for same work equivalent.
*  7) The share of revenue should be decided by bidding for work before allocation of work within the group.
*  8) Before bidding various method of measuring the work should be clearly defined and published in public domain. Any change in process should also be publised in public domain imediately.
*  9) All data related to to revenue sharing should be published as and when generated in public domain.
*  10) For group of people having size more than 10 people will have to make it open online for others to participate in bids. 
*  11) All people involved in related and sub project will be treated as a group.
*  12) Once this license is being used for a certain code then all and any other software services being provided by the group should complusorilly come under 100% revenue sharing.
*  13) Cross subsidization should not happen under this revenue share, no additional benefit should be drawn from the people, by the employing agency.
*  14) Any position or knowledge or legal structure created relating to people working on code, which adversely affects the spirit of 100% revenue share will be treated as a violation.
*  15) Revenue sharing defined.
*     a) includes direct revenue and also intangible gains like brand, and organization.
*     b) No personal judgment should be involved in judging the distribution of revenue.It has to be pre-defined formula based.
*     c)100% of revenue should be shared before expenses and recievers should have control over what expenses to incur.
*     d)For the work done in any month by contributing group member, working on any project can be given revenue share from the work done by the group, for a maximum of 18 months after that month.
*     e)Revenue should be apportioned to the month in which work is done and not when received.
*  The group fulfilling the above conditions also has to be a wiki ecosystem further defined in www.wekosystem.org or read about Wekosystem at www.wikiocean.net

*  ******************************************************************************
*/  
package database;

import java.sql.ResultSet;

import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Vector;
import dbmanager.DBManager;

public class Generatejoin 
{
	DBManager database;
	String tablename="property_details";
	String alies="p";
	String SELECTQUERY="SELECT distinct ";
	String onfield="mid";
	String masteronfield;
	String pltable;
	Vector<Object> MasterCondition =new Vector<Object>();
	Vector<Object> PLCondition =new Vector<Object>();
	
	public void setDatabase(DBManager database)
	{
		this.database = database;
	}
	
	public void setTableName(String tableName,Vector<Object> ConditionVector,String onfield) 
	{
		this.tablename =tableName;
		this.PLCondition.addAll(ConditionVector);
		this.onfield=onfield;
	}
	
	public void setTableName(String Mastertable,String pltable,Vector<Object> MasterCondition,Vector<Object> PLCondition,String masteronfield,String plonfield) 
	{
		this.tablename =Mastertable;
		this.pltable = pltable;	
		this.onfield=plonfield;
		this.masteronfield = masteronfield;
		this.MasterCondition = MasterCondition;
		this.PLCondition = PLCondition;
	}
	
	public String JoinMasterPl(Vector<Object> MasterField,Vector<Object> PLField)
	{
		String QUERY="SELECT distinct ";
		for(int i=0;i<MasterField.size();i++)
		{
			QUERY = QUERY + alies +  "_" + Integer.toString(0) + "."+MasterField.get(i) +  ",";  
		}
		for(int i=0;i<PLField.size();i++)
		{
			QUERY = QUERY + alies + "_" + Integer.toString(2) +"." +PLField.get(i) +  ",";  
		}	
		QUERY = QUERY.substring(0, QUERY.length()-1);
		QUERY = QUERY + " FROM " + tablename + "  "+ alies + "_" + Integer.toString(0) + " LEFT JOIN ";
		if(PLField.size()==0)
			QUERY  = QUERY + pltable + " p_2  ";
		if(PLCondition.size()+MasterCondition.size()>1)
			QUERY = QUERY + " ";
		Generatejoin gj =new Generatejoin();
		gj.SELECTQUERY="";
		gj.setTableName(pltable,PLCondition, onfield);
		gj.getResult(PLField);
		QUERY =QUERY + gj.SELECTQUERY;		
		String conditionstr="";
		String logicaloperator ="";		
		for(int i=0;i<MasterCondition.size();i++)
		{
			Hashtable<Object, Object>conditionhash = new Hashtable<Object, Object>(); 
			conditionhash.putAll((Hashtable<Object, Object>) MasterCondition.get(i));
			if(conditionhash.containsKey("logicaloperator"))
			{	
				logicaloperator = conditionhash.get("logicaloperator").toString();
				conditionhash.remove("logicaloperator");
				conditionstr =  conditionstr +buildCondition(conditionhash, 0) + " " + logicaloperator+" ";
			}
			else
				conditionstr =  conditionstr +buildCondition(conditionhash, 0) ;
		}
		for(int i=0;i<PLCondition.size();i++)
		{
			Hashtable<Object, Object>conditionhash =new Hashtable<Object, Object>();
			conditionhash.putAll((Hashtable<Object, Object>) PLCondition.get(i));
				
			if(conditionhash.containsKey("logicaloperator"))
			{	
				logicaloperator = conditionhash.get("logicaloperator").toString();
				conditionhash.remove("logicaloperator");
				conditionstr =  conditionstr +buildCondition(conditionhash, i+3) + " " + logicaloperator+" ";
			}
			else
				conditionstr =  conditionstr +buildCondition(conditionhash, i+3) + " ";
		}
		if(PLCondition.size()+MasterCondition.size()>1)
		{
			QUERY = QUERY + "  ON "+ alies+ "_" + "0." + masteronfield + "=" + alies + "_" +    "2." + onfield ;
		}	
		else
		{
			QUERY = QUERY + "  ON "+ alies+ "_" + "0." + masteronfield + "=" + alies + "_" +    "2." + onfield ;
			try {
				if(PLCondition.size()+MasterCondition.size()>1)
					conditionstr = conditionstr.substring(0,conditionstr.length()-(logicaloperator.length()+1));
			} catch (Exception e) 
			{
				e.printStackTrace();
			}
		}	
		QUERY = QUERY + " WHERE " + conditionstr;	
		//System.out.println("database:JoinMasterPl:the query is "+QUERY);		
		return (QUERY);		
	}
	
	public String getResult(Vector<Object> tableField)
	{	
		SELECTQUERY = SELECTQUERY  + " " ;
		String condition=" WHERE ";
		try 
		{
			if(PLCondition.size()+MasterCondition.size()==0)
				return "";
			if(PLCondition.size()+MasterCondition.size()==1)
			{	
				for(int i=0;i<tableField.size();i++)
				{
					SELECTQUERY = SELECTQUERY + alies + "_" + Integer.toString(2) +"." +tableField.get(i) +  ",";  
				}
				SELECTQUERY = SELECTQUERY.substring(0, SELECTQUERY.length()-1);
				SELECTQUERY = SELECTQUERY + " FROM ";
				for(int i=0;i<PLCondition.size()+1;i++)
				{
						SELECTQUERY = SELECTQUERY + " " + tablename + " "+ alies +"_"+(i+2) + " LEFT JOIN " ;
				}
				SELECTQUERY = SELECTQUERY.substring(0,SELECTQUERY.length()-10);
				int count=PLCondition.size();
				//SELECTQUERY = SELECTQUERY + tablename ;
				for(int i=0;i<PLCondition.size();i++)
				{
					Hashtable<Object, Object> conditionhash =new Hashtable<Object, Object>();
					conditionhash.putAll((Hashtable<Object, Object>) PLCondition.get(i));
					if(conditionhash.containsKey("conditionoperator"))
					{	
						condition = condition + buildCondition(conditionhash, i+3);						
					}
					else
					{
						condition = condition + buildCondition(conditionhash, i+3);
					}
					SELECTQUERY = SELECTQUERY + " ON  ("+ alies+"_"+Integer.toString(count+1)+"."+onfield+"="+alies+"_"+Integer.toString(count+2)+"."+onfield+")"  ;
					count--;
					if(conditionhash.containsKey("logicaloperator"))
						condition = condition +  conditionhash.get("logicaloperator").toString();
				}
			}
			else if(PLCondition.size()>2)
			{	
				for(int i=0;i<tableField.size();i++)
				{
					SELECTQUERY = SELECTQUERY + alies + "_" + Integer.toString(2) +"." +tableField.get(i) +  ",";  
				}
				for(int i=0;i<PLCondition.size()+1;i++)
				{
					if(i!=0)
						SELECTQUERY = SELECTQUERY + " " + tablename + " "+ alies +"_"+(i+2) + " LEFT JOIN (" ;
					else
						SELECTQUERY = SELECTQUERY + " " + tablename + " "+ alies +"_"+(i+2) + " LEFT JOIN (" ;
					if(i>PLCondition.size()-2)
						SELECTQUERY = SELECTQUERY.substring(0, SELECTQUERY.length()-1);
				}
				SELECTQUERY = SELECTQUERY.substring(0, SELECTQUERY.length()-5);
				String logicaloperator="";
				Hashtable<Object, Object> conditionhash = new Hashtable<Object, Object>(); 
				conditionhash.putAll((Hashtable<Object, Object>) PLCondition.get(0));
				if(conditionhash.containsKey("logicaloperator"))
				{
					logicaloperator = conditionhash.get("logicaloperator").toString();
					conditionhash.remove("logicaloperator");
					condition =condition+ buildCondition(conditionhash, 3)+ " " +logicaloperator + " ";				
				}
				int count=PLCondition.size();
				for(int i=1;i<PLCondition.size();i++)
				{
					conditionhash.putAll((Hashtable<Object, Object>) PLCondition.get(i));
					if(conditionhash.containsKey("logicaloperator"))
					{	
						logicaloperator = conditionhash.get("logicaloperator").toString();
						conditionhash.remove("logicaloperator");
						condition =  condition +buildCondition(conditionhash, i+3) + " " + logicaloperator+" ";
					}
					SELECTQUERY = SELECTQUERY + " ON  ("+ alies+"_"+Integer.toString(count+1)+"."+onfield+"="+alies+"_"+Integer.toString(count+2)+"."+onfield+"))"  ;
					count--;
				}
				conditionhash.putAll((Hashtable<Object, Object>) PLCondition.get(PLCondition.size()-1));
				if(conditionhash.containsKey("logicaloperator"))
				{	
					logicaloperator = conditionhash.get("logicaloperator").toString();
					conditionhash.remove("logicaloperator");
					condition =  condition +buildCondition(conditionhash, PLCondition.size() );
				}
				else
				{
					condition =  condition +buildCondition(conditionhash, PLCondition.size()+1 );
				}
				SELECTQUERY = SELECTQUERY + " ON  ("+ alies+"_"+Integer.toString(2)+"."+onfield+"="+alies+"_"+Integer.toString(PLCondition.size()+1)+"."+onfield+")"  ;
			}	
			else
			{
				SELECTQUERY = SELECTQUERY + " FROM " + tablename + " "+ alies +"_"+0 + " LEFT JOIN (" ;
				for(int i=0;i<PLCondition.size();i++)
				{	
						SELECTQUERY = SELECTQUERY + " " + tablename + " "+ alies +"_"+(i+1) + " LEFT JOIN " ;
				}
				SELECTQUERY = SELECTQUERY.substring(0, SELECTQUERY.length()-10);
				String logicaloperator="";
				Hashtable<Object, Object> conditionhash = new Hashtable<Object, Object>(); 
					conditionhash.putAll((Hashtable<Object, Object>) PLCondition.get(0));
				if(conditionhash.containsKey("logicaloperator"))
				{
					logicaloperator = conditionhash.get("logicaloperator").toString();
					conditionhash.remove("logicaloperator");
					condition =condition+ buildCondition(conditionhash, 0)+ " " +logicaloperator + " ";									
				}
				conditionhash.clear();
				int count = PLCondition.size();
				for(int i=1;i<PLCondition.size();i++)
				{
					conditionhash.putAll((Hashtable<Object, Object>) PLCondition.get(i));
					if(conditionhash.containsKey("logicaloperator"))
					{	
						logicaloperator = conditionhash.get("logicaloperator").toString();
						conditionhash.remove("logicaloperator");
						condition =  condition +buildCondition(conditionhash, i+2) + " " + logicaloperator+" ";
					}
					SELECTQUERY = SELECTQUERY + " ON  ("+ alies+"_"+Integer.toString(count)+"."+onfield+"="+alies+"_"+Integer.toString(count-1)+"."+onfield+"))"  ;				
				}
				
				try 
				{
					conditionhash.putAll((Hashtable<Object, Object>) PLCondition.get(PLCondition.size()-1));
					if(conditionhash.containsKey("logicaloperator"))
					{	
						logicaloperator = conditionhash.get("logicaloperator").toString();
						conditionhash.remove("logicaloperator");
						condition =  condition +buildCondition(conditionhash, PLCondition.size()+2 );
					}
					else
					{
						condition =  condition +buildCondition(conditionhash, PLCondition.size()-1 );
					}
				}
				catch (Exception e) 
				{
					e.printStackTrace();
				}
				SELECTQUERY = SELECTQUERY + " ON  ("+ alies+"_"+Integer.toString(2)+"."+onfield+"="+alies+"_"+Integer.toString(0)+"."+onfield+")"  ;
			}
		} catch (Exception e) 
		{
		
			e.printStackTrace();
		}
		return SELECTQUERY +" "+condition;
	}
	
	
	public String buildCondition(Hashtable<Object, Object> Condition,int i)
	{
		String query=" ";			
		Enumeration<Object>enumforhash= Condition.keys();
		boolean flag=false;
		String conditionoperator = Condition.get("conditionoperator").toString();
		Condition.remove("conditionoperator");
		while(enumforhash.hasMoreElements())
		{
			String key = enumforhash.nextElement().toString();			
				query = query+ " " + alies + "_"  + Integer.toString(i)+ "." + key + conditionoperator + "'"+ Condition.get(key) + "'"+ " AND"  ;
		}
		
		return query.substring(0, query.length()-4);
	}
	
	public static void main(String[] args) 
	{
		Hashtable<Object, Object> table =new Hashtable<Object, Object>();
		Vector<Object> condition = new Vector<Object>();
		
		/*table.put("pid", "9");
		table.put("pv", "25");
		table.put("logicaloperator", "and");
		condition.add(table);		
		Hashtable<Object, Object> table1 =new Hashtable<Object, Object>();
		table1.put("pid", "7");
		table1.put("pv", "549");
		table1.put("logicaloperator", "and");
		condition.add(table1);
		Hashtable<Object, Object> table2 =new Hashtable<Object, Object>();		
		table2.put("pid", "6");
		table2.put("pv", "5875");
		condition.add(table2);
		Generatejoin gj =new Generatejoin();
		//gj.setTableName("property_details", condition, "mid");
		Vector<Object> masterfield =new Vector<Object>();
		masterfield.add("id");
		masterfield.add("name");
		Vector<Object> plfield =new Vector<Object>();
		plfield.add("mid");
		plfield.add("pid");
		plfield.add("pv");
		plfield.add("vt");
		plfield.add("ref");
		gj.setTableName("master_table", "property_details",new Vector<Object>(), condition, "id","mid");
		*/
		table.put("rate", "10");
		//table.put("conditionoperator", "=");
		table.put("logicaloperator", "and");
		condition.add(table);
		Hashtable<Object, Object> table1 =new Hashtable<Object, Object>();
		table1.put("buysell", "217");
		condition.add(table1);	
		//condition.add(table);	
		Generatejoin gj =new Generatejoin();
		gj.setTableName("rate_master", condition,"id");
	//	System.out.println(gj.getResult());
		
	}
}
